package gestionBulletins.model.persistance.sqliteImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import gestionBulletins.model.Adherent;
import gestionBulletins.model.Section;
import gestionBulletins.model.persistance.DaoSection;

/**
 * Implémentation du DAO Section pour le type de persistance SQLite
 * @author Paraita Wohler
 *
 */
public class DaoSectionSqliteImpl implements DaoSection {

	private Connection conn = null;
	private static String insert = "insert into sectioncommune (president) values (?);";
	private static String reqByPresident = "select * from sectioncommune where president=?";
	private static String reqByAdherent = "select sectioncommune.id,sectioncommune.president " +
										  "from sectioncommune join adherent on " +
										  "adherent.sect=sectioncommune.id where " +
										  "adherent.nom=? and adherent.prenom1=?;";
	private static String update = "update sectioncommune set president=? " +
								   "where id=?;";
	private static String delete = "delete from sectioncommune where id=?;";
	private static String getallpresidents = "select president from sectioncommune;";
	private static String getAllSections = "select * from sectioncommune;";
	
	public DaoSectionSqliteImpl(Connection conn) {
		this.conn = conn;
	}
	
	/* (non-Javadoc)
	 * @see gestionBulletins.model.persistance.DaoSection#createSection(gestionBulletins.model.Section)
	 */
	@SuppressWarnings("finally")
	@Override
	public Section createSection(Section section) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			/* on verifie si une section avec ce president n'existe pas deja */
			ps = conn.prepareStatement(reqByPresident);
			ps.setString(1, section.getPresident());
			rs = ps.executeQuery();
			if (rs.next()) {
				section = null;
				rs.close();
			}
			/* sinon tout se passe bien on cree la section et la recupere */
			else {
				ps.close();
				rs.close();
				ps = conn.prepareStatement(insert);
				ps.setString(1, section.getPresident());
				ps.execute();
				ps.close();
				ps = conn.prepareStatement(reqByPresident);
				ps.setString(1, section.getPresident());
				rs = ps.executeQuery();
				if (rs.next()) {
					section.setId(rs.getInt("id"));
				} else {
					section = null;
				}
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return section;
		}
	}

	/* (non-Javadoc)
	 * @see gestionBulletins.model.persistance.DaoSection#requestById(int)
	 */
	@SuppressWarnings("finally")
	@Override
	public Section requestById(int id) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Section section = null;
		try {
			ps = conn.prepareStatement(reqByPresident);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				section = new Section(rs.getString("president"));
				section.setId(rs.getInt("id"));
				rs.close();
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return section;
		}
	}

	/* (non-Javadoc)
	 * @see gestionBulletins.model.persistance.DaoSection#update(gestionBulletins.model.Section)
	 */
	@Override
	public void update(Section section) {
		//System.out.println("oui c'est bien moi !");
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(update);
			ps.setString(1, section.getPresident());
			ps.setInt(2, section.getId());
			ps.execute();
			ps.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		};
	}

	/* (non-Javadoc)
	 * @see gestionBulletins.model.persistance.DaoSection#delete(gestionBulletins.model.Section)
	 */
	@Override
	public void delete(Section section) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(delete);
			ps.setInt(1, section.getId());
			ps.execute();
			ps.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@SuppressWarnings("finally")
	@Override
	public Section requestByPresident(String president) {
		Section section = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(reqByPresident);
			ps.setString(1, president);
			rs = ps.executeQuery();
			if (rs.next()) {
				section = new Section(rs.getString("president"));
				section.setId(rs.getInt("id"));
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return section;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public Section requestByAdherent(Adherent adherent) {
		Section section = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(reqByAdherent);
			ps.setString(1, adherent.getNom());
			ps.setString(2, adherent.getPrenom1());
			rs = ps.executeQuery();
			if (rs.next()) {
				section = new Section(rs.getString("president"));
				section.setId(rs.getInt("id"));
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return section;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public Collection<String> getAllPresident() {
		ArrayList<String> presidents = new ArrayList<String>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(getallpresidents);
			rs = ps.executeQuery();
			while (rs.next()) {
				presidents.add(rs.getString("president"));
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return presidents;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public ArrayList<Section> getAllSections() {
		ArrayList<Section> sections = new ArrayList<Section>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Section cur = null;
		try {
			ps = conn.prepareStatement(getAllSections);
			rs = ps.executeQuery();
			while (rs.next()) {
				cur = new Section(rs.getString("president"));
				cur.setId(rs.getInt("id"));
				sections.add(cur);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return sections;
		}
	}

}
